package cn.edu.zafu.bigdata.YinMingyan.chapter12;


import java.sql.*;

public class DataBaseFunction {
    //jdbc:mysql://192.168.169.76:3306
    private static final String URL = "jdbc:mysql://192.168.169.76:3306/learnjava";
    private static final String USER = "learn";
    private static final String PASSWORD = "learnpassword";

    // 获取数据库连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    // 创建学生信息表
    public static void createTable(Connection conn) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS students0105(" +
                "id VARCHAR(255) PRIMARY KEY, " +
                "name VARCHAR(255), " +
                "phone VARCHAR(255))";
        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
        }
    }

    // 添加学生信息
    public static void addStudent(Connection conn, Student student) throws SQLException {
        String sql = "INSERT INTO students0105 (id, name, phone) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, student.getId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getPhone());
            pstmt.executeUpdate();
        }
    }

    // 检索学生信息
    public static Student findStudent(Connection conn, String id) throws SQLException {
        String sql = "SELECT * FROM students0105 WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new Student(rs.getString("id"), rs.getString("name"), rs.getString("phone"));
                }
            }
        }
        return null;
    }

    // 更新学生信息
    public static void updateStudent(Connection conn, Student student) throws SQLException {
        String sql = "UPDATE students0105 SET name = ?, phone = ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getPhone());
            pstmt.setString(3, student.getId());
            pstmt.executeUpdate();
        }
    }

    // 删除学生信息
    public static void deleteStudent(Connection conn, String id) throws SQLException {
        String sql = "DELETE FROM students0105 WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            pstmt.executeUpdate();
        }
    }
}